USE main
GO

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name='SCH')
	EXEC('CREATE SCHEMA SCH')
GO
IF(NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCH].[TargetIndex]') AND type in (N'U')))
BEGIN
CREATE TABLE SCH.TargetIndex
(
	ID tinyint not null,
	Name varchar(100),
	CONSTRAINT [PK_TargetIndex] PRIMARY KEY CLUSTERED (ID)
)
EXEC('INSERT INTO SCH.TargetIndex (ID, Name)
	VALUES
	(1,''Client'');
    INSERT INTO SCH.TargetIndex (ID, Name)
	VALUES
	(2,''Main'');')

END

IF(NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCH].[ChangeIndex]') AND type in (N'U')))
BEGIN
CREATE TABLE SCH.ChangeIndex
(
	 ID numeric(16,3) not null,
	 changetext nvarchar(max),
	 disable bit not null constraint def_ChangeIndex_disable DEFAULT 0,
	 rerunfail bit not null constraint def_ChangeIndex_rerunfailed DEFAULT 0,
	 targetID tinyint CONSTRAINT fk_ChangeIndex_targetID FOREIGN KEY REFERENCES SCH.TargetIndex(Id),
	 clientID int,
	 dCreate datetime not null constraint def_ChangeIndex_dCreate DEFAULT GETUTCDATE(),
	 CONSTRAINT [PK_ChangeIndex] PRIMARY KEY CLUSTERED (ID),
 )
END

--all tables

IF(NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[SCH].[Changes]') AND type in (N'U')))
BEGIN
CREATE TABLE SCH.Changes
(
	changeID numeric(16,3) not null,
	dSuccess datetime,
	dFail datetime,
	failmsg varchar(max)
)
END

-- main version, auth changes the default targetID to AUTH!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

IF EXISTS (	SELECT	name 
			FROM	sys.objects 
			WHERE	name = N'RegisterSchemaChange' 
			AND		SCHEMA_NAME(schema_id) = 'SCH'
			AND		type = 'P')
	DROP PROCEDURE SCH.RegisterSchemaChange

go
CREATE PROCEDURE SCH.RegisterSchemaChange
(
	@changeID numeric(16,3),
	@changeText nvarchar(max),
	@targetID tinyint, -- 1 = client, 2 = main, 3 store, 4 = auth
	@disable bit = 0,
	@rerunfail bit = 0,
	@clientID int = null,
	@overwrite bit = 0
)
AS
BEGIN
	IF @disable = 1
		UPDATE SCH.ChangeIndex SET disable=1 WHERE ID=@changeID
	ELSE
		BEGIN
			IF @overwrite=1
			BEGIN
				DELETE FROM SCH.ChangeIndex WHERE ID=@changeID
				SET @rerunfail=1
			END

			IF NOT EXISTS(SELECT TOP 1 * FROM SCH.ChangeIndex WHERE ID=@changeID)
				INSERT INTO SCH.ChangeIndex (ID, changetext, disable, rerunfail, targetid, clientid)
				VALUES (@changeID, @changeText, 0, @rerunfail, @targetID, @clientID)
		END


END --End of Procedure

GO

GRANT EXECUTE ON SCH.RegisterSchemaChange to public
go



-- main version
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

IF EXISTS (	SELECT	name 
			FROM	sys.objects 
			WHERE	name = N'ExecuteSchemaChanges' 
			AND		SCHEMA_NAME(schema_id) = 'SCH'
			AND		type = 'P')
	DROP PROCEDURE SCH.ExecuteSchemaChanges

go
CREATE PROCEDURE SCH.ExecuteSchemaChanges
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @targetID tinyint
	DECLARE @clientID int 
	DECLARE @sqlCommand nvarchar(max)
	DECLARE @changeID numeric(16,3)
	declare @errmsg varchar(max)
	declare @db_name varchar(100) 

	SELECT  @db_name=DB_NAME();
	SET @targetID=1;
	SET @clientID=0;

	IF @DB_NAME='main'
		SET @targetID=2	
	ELSE
		SELECT @clientID = ID FROM main.dbo.ClientIndex WHERE dbname=DB_NAME()
		
	-- get rid of changes which have been disabled but not ran
	-- or those which have failed but are marked as rerunfail
	DELETE SCH.Changes
		FROM SCH.Changes changes
		INNER JOIN main.SCH.ChangeIndex ci ON changes.changeID=ci.ID AND ci.targetID=@targetID AND (ci.clientID IS NULL OR ci.clientID=@clientID)
		WHERE (ci.Disable=1 AND changes.dsuccess IS NULL) OR (ci.ReRunFail=1 AND changes.dfail IS NOT NULL)
	
	INSERT INTO SCH.Changes
	(changeID)
	SELECT ID
		FROM main.SCH.ChangeIndex ci
			LEFT OUTER JOIN SCH.Changes changes ON ci.ID=changes.changeID
		WHERE ci.targetID=@targetID AND (ci.clientID IS NULL OR ci.clientID=@clientID)
			AND changes.changeID IS NULL
	
	DECLARE sqlCommands CURSOR LOCAL FAST_FORWARD FOR
	SELECT changeID
		FROM SCH.Changes changes
		WHERE changes.dSuccess IS NULL
		ORDER BY changes.changeID
	OPEN sqlCommands
	FETCH NEXT FROM sqlCommands INTO @changeID
	
	WHILE @@FETCH_STATUS=0
	BEGIN
		SELECT @sqlCommand = changeText FROM main.SCH.ChangeIndex WHERE ID=@changeID
		
		begin try
			EXEC (@sqlCommand)
			UPDATE SCH.Changes SET dSuccess = GETUTCDATE(), dFail = NULL WHERE changeID=@changeID
		end try
		begin catch
			set @errmsg = 'Error in '+ISNULL(ERROR_PROCEDURE(),'SCH.ExecuteSchemaChanges')+' for Schema ID ' + CONVERT(varchar,@changeID) + ' at Line Number: ' + CONVERT(varchar,Error_Line()) + ' and message is ' + ERROR_MESSAGE()
			CLOSE sqlCommands
			DEALLOCATE sqlCommands
			UPDATE SCH.Changes SET dFail = GETUTCDATE(), failmsg = @errmsg WHERE changeID=@changeID
			SET NOCOUNT OFF
			raiserror(@errmsg,16,1)
			RETURN
		END CATCH
		
		FETCH NEXT FROM sqlCommands INTO @changeID
	END
	
	CLOSE sqlCommands
	DEALLOCATE sqlCommands

	SET NOCOUNT OFF
END --End of Procedure

GO

GRANT EXECUTE ON SCH.ExecuteSchemaChanges to public
go
